﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Backend;
namespace DBLayer
{
    public class DBTaxiRequest
    {
        SqlConnection conn;

        public DBTaxiRequest()
        {
            conn = DBConnection.getConnection().getSQLConnection();

        }
              

        public bool SaveTaxiRequest(TaxiRequest TaxiRequest)
        {
            try
            {
                conn.Open();
                String Query = "use dm77_1 insert into TaxiRequests(ID,phoneNr, address, status) values(" + TaxiRequest.Id + ",'" + TaxiRequest.PhoneNr +"', '" + TaxiRequest.Address + "', " + TaxiRequest.Status + ")";
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();
                
                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the TaxiRequest");
                Console.WriteLine(ex.Message);
                
                return false;

            }

            finally{
                conn.Close();
            }


        
        }


        public bool UpdateTaxiRequest(int id, int status)
        {
            try
            {
                conn.Open();
                String Query = "use dm77_1 update TaxiRequests  set status=" + status + " where id=" + id;
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the TaxiRequest");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                conn.Close();
            }



        }

        public bool UpdateTaxiRequestWithPlateNr(int id, string plateNr)
        {
            try
            {
                conn.Open();
                String Query = "use dm77_1 update TaxiRequests  set plateNumber='" + plateNr + "' where id=" + id;
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the TaxiRequest");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                conn.Close();
            }



        }


        public bool UpdateTaxiRequestWithEstimatedTime(int id, string estimatedTime)
        {
            try
            {
                conn.Open();
                String Query = "use dm77_1 update TaxiRequests  set estimatedTime='" + estimatedTime + "' where id=" + id;
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the TaxiRequest");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                conn.Close();
            }



        }


        public bool DeleteTaxiRequest(int id)
        {
            try
            {
                conn.Open();
                String Query = "use dm77_1 delete from TaxiRequests where id=" + id;
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the TaxiRequest");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                conn.Close();
            }



        }


       


        public TaxiRequest GetTaxiRequest(int id)
        {
            try
            {
                conn.Open();
                TaxiRequest taxiRequest = new TaxiRequest();
                String Query = "use dm77_1 select * from TaxiRequests where id =" + id ;
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {

                    taxiRequest.Id = (int)reader["id"];
                    taxiRequest.Status = (int)reader["status"];
                    taxiRequest.PhoneNr = reader["phoneNr"].ToString();
                    taxiRequest.Address = reader["address"].ToString();
                    taxiRequest.Date = reader["date"].ToString();
                    taxiRequest.PlateNr = reader["plateNumber"].ToString();
                    taxiRequest.EstimatedTime = reader["estimatedTime"].ToString();
               

                }
                
                reader.Close();
                return taxiRequest;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the TaxiRequest");
                Console.WriteLine(ex.Message);
                
                return null;

            }

            finally
            {
                conn.Close();
            }
        }


        public List<TaxiRequest> getTaxiRequestsByStatus(int status)
        {
            List<TaxiRequest> taxiRequests = new List<TaxiRequest>();
            string query = "Select * from TaxiRequests where status = " + status;
            SqlDataReader reader = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    TaxiRequest taxiRequest = new TaxiRequest();
                    taxiRequest.Id = (int)reader["id"];
                    taxiRequest.Status = (int)reader["status"];
                    taxiRequest.PhoneNr = reader["phoneNr"].ToString();
                    taxiRequest.Address = reader["address"].ToString();
                    taxiRequest.Date = reader["date"].ToString();
                    taxiRequest.PlateNr = reader["plateNumber"].ToString();
                    taxiRequest.EstimatedTime = reader["estimatedTime"].ToString();
                   
                    taxiRequests.Add(taxiRequest);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return taxiRequests;
        }

        public bool SaveUser(User user)
        {
            try
            {
                conn.Open();
                String Query = "use dm77_1 insert into Users(phoneNr, homeAddress) values('" + user.PhoneNr + "','" + user.HomeAddress + "')";
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with adding the User");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                conn.Close();
            }



        }



        public bool UpdateUser(User user)
        {
            try
            {
                conn.Open();
                String Query = "use dm77_1 update Users set homeAddress='" +  user.HomeAddress + "'" + " where phoneNr ='" +user.PhoneNr + "'";
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with adding the User");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                conn.Close();
            }



        }


        public User GetUser(String phoneNr)
        {
            try
            {
                conn.Open();
                User user = new User(); 
                String Query = "use dm77_1 select * from Users where phoneNr ='" + phoneNr + "'";
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, conn);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {

                    user.PhoneNr = reader["phoneNr"].ToString();
                    user.HomeAddress = reader["homeAddress"].ToString();

                }

                reader.Close();
                return user;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the TaxiRequest");
                Console.WriteLine(ex.Message);

                return null;

            }

            finally
            {
                conn.Close();
            }
        }


       
    }
}
